if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_calculate_overtime')
          and type in ('P','PC'))
   drop procedure bd_proc_calculate_overtime
go


create procedure bd_proc_calculate_overtime (@deptIds varchar(max) = '',@userIds varchar(max) = '',@yearAndMonth varchar(7),@operator varchar(50)) as
BEGIN
	--加班计算存储过程
	--思路：根据传值，确定要对哪些人，哪个月进行加班计算，放入临时表#calculate_table,过滤掉不合格的（放到#return_talbe中，返回），剩余的人先删除旧的数据，在添加
			--之后,针对#calculate_table 的人员和年月日期，统计上月结余、本月加班（平时、周末、节假日）、放班、请假、调休（生效的）。统计到bd_kq_ot_settle

		 ----抵扣思路： 得到要加班支付的记录，之后分别做放班抵扣，事假抵扣，调休抵扣，计算当月，计算上月，计算结余逻辑操作，并更新bd_kq_ot_serttle表，
				--最后将最终结果插入或更新时间银行表bd_kq_holidays_time

	--逻辑：
				--放班抵扣：放班抵扣本月双休日加班-->本月平时加班，结果为正或0（加班-放班），本月无欠工；结果为负，负数绝对值为本月欠工，
					--本月双休加班和平时加班剩余为0，当调休抵扣之后，本月欠工要抵扣按上月双休日加班-->上月平时加班扣除顺序（逻辑同放班抵扣）
				--事假抵扣：抵扣前提是放班抵扣结果为正，剩余加班按本月双休日加班-->本月平时加班抵扣，结果为正或0（加班-事假），事假抵扣完毕
					--抵扣后剩余请假字段为0，结果为负，则负数绝对值为抵扣后剩余请假字段值，本月双休加班和平时加班剩余为0
				--调休抵扣：抵扣上月结余，上月双休日加班-->上月平时加班，不存在调休超限的情况。
				--计算当月：根据值或比例将本月的平时加班剩余和双休加班剩余分配给支付工作和存工；
				--计算上月：根据值或比例将本月的平时加班剩余和双休加班剩余分配给支付工作和存工，若@isHoliday 是0 ,即不分配，则默认全部分配给存工
				--计算结余；将当月平时加班和上月剩余平时加班存工相加，本月双休加班和上月剩余双休加班相加，最终欠工 = 上月欠工+本月欠工。
	--参数
		DECLARE @beginDate date
		DECLARE @endDate date

		DECLARE @settleDayEnable int --是否启用
		DECLARE @beginDay int
		DECLARE @endDay int




		--计算时间区间设置

		select top 1 @settleDayEnable = settle_day_enable, @beginDay = settle_day_begin, @endDay = settle_day_end from bd_sys_global_params;
		--启用配置区间
		if @settleDayEnable=1
			BEGIN
				set @beginDate = DATEADD(day, @beginDay-1, DATEADD(mm, -1, convert(date,@yearAndMonth+'-01')))
				set @endDate = convert(date,concat(@yearAndMonth,'-',@endDay))
			END
		--使用自然月区间
		else
			BEGIN
				set @beginDate = convert(date,@yearAndMonth+'-01')
				set @endDate = DATEADD(day, -1, DATEADD(mm, 1, convert(date,@yearAndMonth+'-01')))
			END

	--临时表

	create table #calculate_table(
		user_serial numeric not null
	)

	--创建 返回不满足请假的人的原因
	create table #return_table(
		user_serial numeric not null ,
		user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
		user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
		dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
		error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS not NULL
	)
	--本次插入的记录ID
	create table #settle(
		id bigint not null
	)


	begin try
		begin tran

			--1、根据传值，确定要对哪些人，哪个月进行加班计算，放入临时表#calculate_table
			if @deptIds != ''
				BEGIN
					insert into #calculate_table
						select u.user_serial from string_split(@deptIds,',') a
							INNER JOIN dt_user u on a.[value] = u.user_dep
							where  u.user_type<51;
				END
			ELSE
				BEGIN
					if @userIds != ''
						BEGIN
							insert into #calculate_table
							select * from string_split(@userIds,',');
						END
				END

			--2、过滤掉不合格的（放到#return_talbe中，返回）

			insert into #return_table
			select
				u.user_serial,
				u.user_no,
				u.user_lname,
				d.dep_name,
				'考勤员未封帐，不能重新加班计算'
				from #calculate_table t
				INNER JOIN dt_user u on t.user_serial = u.user_serial
				INNER JOIN dt_dep d on u.user_dep = d.dep_serial
				left JOIN bd_kq_accounts a on u.user_serial = a.user_serial
							and a.year = DATEPART(year, @yearAndMonth+'-01')
							and a.month = DATEPART(month, @yearAndMonth+'-01')
				where isnull(a.lock_status,0)<1
			--从#settle中移除不满足条件的人的单子id
			delete t from #calculate_table t INNER JOIN #return_table r on r.user_serial = t.user_serial


			insert into #return_table
			select
				u.user_serial,
				u.user_no,
				u.user_lname,
				d.dep_name,
				'HR已封帐，不能重新加班计算'
				from #calculate_table t
				INNER JOIN dt_user u on t.user_serial = u.user_serial
				INNER JOIN dt_dep d on u.user_dep = d.dep_serial
				left JOIN bd_kq_accounts a on u.user_serial = a.user_serial
							and a.year = DATEPART(year, @yearAndMonth+'-01')
							and a.month = DATEPART(month, @yearAndMonth+'-01')
				where isnull(a.lock_status,0)>1

			--从#settle中移除不满足条件的人的单子id
			delete t from #calculate_table t INNER JOIN #return_table r on r.user_serial = t.user_serial


			--3、先删除旧的数据,	后初始化数据
			delete s from bd_kq_ot_settle s
				INNER JOIN #calculate_table c on s.user_serial = c.user_serial
				where s.settle_year = DATEPART(year, @yearAndMonth+'-01')  and s.settle_month = DATEPART(month, @yearAndMonth+'-01');

			insert into bd_kq_ot_settle(user_serial,user_name,user_dept,settle_year,settle_month,operateId,operateTime)
				select c.user_serial,u.user_lname,d.dep_serial,DATEPART(year, @yearAndMonth+'-01'),DATEPART(month, @yearAndMonth+'-01'),
							@operator,getdate()
				from #calculate_table c
				INNER JOIN dt_user u on u.user_serial = c.user_serial
				INNER JOIN dt_dep d on d.dep_serial = u.user_dep
			--4、统计上月结余、本月加班（平时、周末、节假日）、放班、请假、调休（生效的）。统计到bd_kq_ot_settle
			--a.上月结余
			update s
				set s.prev_month_ordinary = a.save_ordinary,
						s.prev_month_weekend = a.save_weekend,
						s.prev_month_owe = a.owe
				from bd_kq_ot_settle s
				INNER JOIN (
					select c.user_serial,t.save_ordinary,t.save_weekend,t.owe
					from  bd_kq_holidays_time t
					INNER JOIN #calculate_table c on t.user_serial = c.user_serial
					where t.time_year = DATEPART(year, DATEADD(month, -1,  @yearAndMonth+'-01'))
						and t.time_month = DATEPART(month, DATEADD(month, -1,  @yearAndMonth+'-01'))
				) a on s.user_serial = a.user_serial
				where s.settle_year = DATEPART(year, @yearAndMonth+'-01')  and s.settle_month = DATEPART(month, @yearAndMonth+'-01');

			--b.本月加班（平时、周末、节假日）,bd_kq_timecard 指定日期按人分组求和
			update s
				set s.cur_month_ordinary = a.ot_normal_hours,
						s.cur_month_weekend = a.ot_weekend_hours,
						s.cur_month_statutory = a.ot_holiday_hours
				from bd_kq_ot_settle s
				INNER JOIN (
					select c.user_serial,
							sum(t.ot_normal_hours) as ot_normal_hours ,
							sum(t.ot_weekend_hours) as ot_weekend_hours ,
							sum(t.ot_holiday_hours) as ot_holiday_hours
					from  bd_kq_timecard t
					INNER JOIN #calculate_table c on t.user_serial = c.user_serial
					where t.card_date BETWEEN @beginDate and @endDate
					group by c.user_serial
				) a  on s.user_serial = a.user_serial
				where s.settle_year = DATEPART(year, @yearAndMonth+'-01')  and s.settle_month = DATEPART(month, @yearAndMonth+'-01');

			--c.放班，请假明细表lr_kqingj 指定日期和放班类型、状态生效，按人分组求和
			update s
				set s.cur_month_fangban = a.jsjg
				from bd_kq_ot_settle s
				INNER JOIN (
					select c.user_serial,sum(k.jsjg) as jsjg
					from  lr_kqingj k
					INNER JOIN #calculate_table c on k.user_serial = c.user_serial
					INNER JOIN bd_leave_order o on o.order_id = k.order_id
					where convert(varchar(10),k.kssj,120) BETWEEN convert(varchar(10),@beginDate,120) and convert(varchar(10),@endDate,120)
								and o.status in (3,4) and o.is_del = 0 and k.whether_validity = 1 and k.bh = '00113'
					group by c.user_serial
				) a  on s.user_serial = a.user_serial
				where s.settle_year = DATEPART(year, @yearAndMonth+'-01')  and s.settle_month = DATEPART(month, @yearAndMonth+'-01');

			--d.请假，请假明细表lr_kqingj 指定日期和请假类型、状态生效，按人分组求和
			update s
				set s.cur_month_fangban = a.jsjg
				from bd_kq_ot_settle s
				INNER JOIN (
					select c.user_serial,sum(k.jsjg) as jsjg
					from  lr_kqingj k
					INNER JOIN #calculate_table c on k.user_serial = c.user_serial
					INNER JOIN bd_leave_order o on o.order_id = k.order_id
					where convert(varchar(10),k.kssj,120) BETWEEN convert(varchar(10),@beginDate,120) and convert(varchar(10),@endDate,120)
								and o.status in (3,4) and o.is_del = 0 and k.whether_validity = 1 and k.bh = '00108'
					group by c.user_serial
				) a  on s.user_serial = a.user_serial
				where s.settle_year = DATEPART(year, @yearAndMonth+'-01')  and s.settle_month = DATEPART(month, @yearAndMonth+'-01');

			--e.调休，请假明细表lr_kqingj 指定日期和调休类型、状态生效，按人分组求和
			update s
				set s.cur_month_fangban = a.jsjg
				from bd_kq_ot_settle s
				INNER JOIN (
					select c.user_serial,sum(k.jsjg) as jsjg
					from  lr_kqingj k
					INNER JOIN #calculate_table c on k.user_serial = c.user_serial
					INNER JOIN bd_leave_order o on o.order_id = k.order_id
					where convert(varchar(10),k.kssj,120) BETWEEN convert(varchar(10),@beginDate,120) and convert(varchar(10),@endDate,120)
								and o.status in (3,4) and o.is_del = 0 and k.whether_validity = 1 and k.bh = '00105'
					group by c.user_serial
				) a  on s.user_serial = a.user_serial
				where s.settle_year = DATEPART(year, @yearAndMonth+'-01')  and s.settle_month = DATEPART(month, @yearAndMonth+'-01');
			--5、计算抵扣
				--1、将要计算支付的记录id 存入#serttle
				insert into #settle
					select s.id from bd_kq_ot_settle s
					INNER JOIN #calculate_table c on s.user_serial = c.user_serial
					where s.settle_year = DATEPART(year, @yearAndMonth+'-01')  and s.settle_month = DATEPART(month, @yearAndMonth+'-01');

				--2、放班抵扣
				update s
					set cur_month_deduction_weekend = case when cur_month_weekend - cur_month_fangban >= 0
								then cur_month_weekend - cur_month_fangban else 0 end ,
							cur_month_deduction_ordinary = case when cur_month_weekend - cur_month_fangban<0 then
								(case when cur_month_ordinary+cur_month_weekend-cur_month_fangban >= 0
								then cur_month_ordinary+cur_month_weekend-cur_month_fangban else 0 end) else cur_month_ordinary end,
							cur_month_owe = case when cur_month_ordinary+cur_month_weekend-cur_month_fangban <0
								then abs(cur_month_ordinary+cur_month_weekend-cur_month_fangban) else 0 end
				from bd_kq_ot_settle s
				inner join #settle e on s.id = e.id
				--3、上月欠工抵扣
				update s
					set cur_month_deduction_weekend = case when cur_month_deduction_weekend - prev_month_owe >= 0
								then cur_month_deduction_weekend - prev_month_owe else 0 end ,
							cur_month_deduction_ordinary = case when cur_month_deduction_weekend - prev_month_owe<0 then
								(case when cur_month_deduction_ordinary+cur_month_deduction_weekend - prev_month_owe >= 0
								then cur_month_deduction_ordinary+cur_month_deduction_weekend - prev_month_owe else 0 end) else cur_month_deduction_ordinary end,
							cur_month_owe = cur_month_owe + case when cur_month_deduction_ordinary+cur_month_deduction_weekend - prev_month_owe <0
								then abs(cur_month_deduction_ordinary+cur_month_deduction_weekend - prev_month_owe) else 0 end
				from bd_kq_ot_settle s
				inner join #settle e on s.id = e.id

				--4、事假抵扣
				update s
					set cur_month_deduction_weekend = case when cur_month_deduction_weekend - cur_month_leave >= 0 then
								cur_month_deduction_weekend - cur_month_leave else 0 end ,
							cur_month_deduction_ordinary = case when cur_month_deduction_weekend - cur_month_leave<0 then
								(case when cur_month_deduction_ordinary+cur_month_deduction_weekend - cur_month_leave >= 0 then
								cur_month_deduction_ordinary+cur_month_deduction_weekend - cur_month_leave else 0 end) else
								cur_month_deduction_ordinary end,
							cur_month_result_leave = case when
								cur_month_deduction_ordinary+cur_month_deduction_weekend - cur_month_leave < 0
								then abs(cur_month_deduction_ordinary+cur_month_deduction_weekend - cur_month_leave) else 0 end
				from bd_kq_ot_settle s
				inner join #settle e on s.id = e.id

				--5、调休抵扣
				update s
					set prev_month_deduction_weekend = case when prev_month_weekend - cur_month_rest >= 0
								then prev_month_weekend - cur_month_rest else 0 end ,
							prev_month_deduction_ordinary = case when prev_month_weekend - cur_month_rest<0 then
								prev_month_ordinary+prev_month_weekend - cur_month_rest else prev_month_ordinary end
				from bd_kq_ot_settle s
				inner join #settle e on s.id = e.id

				--6、本月欠工抵扣
				update s
					set prev_month_deduction_weekend = case when prev_month_deduction_weekend - cur_month_owe >= 0
								then prev_month_deduction_weekend - cur_month_owe else 0 end ,
							prev_month_deduction_ordinary = case when prev_month_deduction_weekend - cur_month_owe<0 then
								(case when prev_month_deduction_ordinary+prev_month_deduction_weekend - cur_month_owe >=0 then
								prev_month_deduction_ordinary+prev_month_deduction_weekend - cur_month_owe else 0 end ) else
								prev_month_deduction_ordinary end,
							cur_month_owe = case when prev_month_deduction_ordinary+prev_month_deduction_weekend - cur_month_owe <0
								then abs(prev_month_deduction_ordinary+prev_month_deduction_weekend - cur_month_owe) else 0 end
				from bd_kq_ot_settle s
				inner join #settle e on s.id = e.id


				--7、返回错误数据
				select * from #return_table
			commit tran
		end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END
			RAISERROR (@errStr, 16, 1);
	end catch
END
go
